R Notebook

#install.packages("knitr")
#install.packages("grid")
#install.packages("MLRMPA")
#install.packages("dprep")
#install.packages("normalr")
#install.packages("ggcorrplot")
#install.packages("RColorBrewer")
#install.packages("rgdal")
#install.packages("jsonlite")
#install.packages("RColorBrewer")
#install.packages("readr")
Sys.setlocale("LC_ALL", "English")
Sys.setenv("LANGUAGE"="En")

library(gridExtra)

Warning message:
Unknown or uninitialised column: `Country`. 

library(dplyr)

Attaching package: 㤼㸱dplyr㤼㸲

The following object is masked from 㤼㸱package:gridExtra㤼㸲:

    combine

The following objects are masked from 㤼㸱package:stats㤼㸲:

    filter, lag

The following objects are masked from 㤼㸱package:base㤼㸲:

    intersect, setdiff, setequal, union

library(lubridate)

Attaching package: 㤼㸱lubridate㤼㸲

The following objects are masked from 㤼㸱package:base㤼㸲:

    date, intersect, setdiff, union

library(magrittr)
library(ggplot2)
library(tidyr)

Attaching package: 㤼㸱tidyr㤼㸲

The following object is masked from 㤼㸱package:magrittr㤼㸲:

    extract

library(knitr)
library(normalr)

package 㤼㸱normalr㤼㸲 was built under R version 4.0.3

library(ggcorrplot)
library(leaflet)

package 㤼㸱leaflet㤼㸲 was built under R version 4.0.3

library(plotly)

package 㤼㸱plotly㤼㸲 was built under R version 4.0.3
Attaching package: 㤼㸱plotly㤼㸲

The following object is masked from 㤼㸱package:ggplot2㤼㸲:

    last_plot

The following object is masked from 㤼㸱package:stats㤼㸲:

    filter

The following object is masked from 㤼㸱package:graphics㤼㸲:

    layout

library(RColorBrewer)

package 㤼㸱RColorBrewer㤼㸲 was built under R version 4.0.3

library(readr)

package 㤼㸱readr㤼㸲 was built under R version 4.0.3

#??src_mysql
my_db <- src_mysql(
  dbname = "covid",
  host = "localhost",
  user = "root",
  password = "1234"
)

`src_mysql()` is deprecated as of dplyr 1.0.0.
Please use `tbl()` directly with a database connection
This warning is displayed once every 8 hours.
Call `lifecycle::last_warnings()` to see where this warning was generated.

my_db

src:  mysql 8.0.21 [root@localhost:/covid]
tbls: covid_thailand_updated, covid19_confirmed, covid19_confirmed_global, covid19_deaths, covid19_deaths_global, covid19_recovered,
  covid19_recovered_global, covidus, data_ethnic, data_gender, data_health, data_lockdown, data_population, data_testing, full_datas, gdp,
  gdp19, healthranking, imf-country, owid-covid-data, population, pornhub, sars_2003, us_gdp, us_homeless, us_latlong, world_temp

##import data
df_conf <- tbl(my_db, sql("select * from covid19_confirmed"))
df_conf <- as.data.frame(df_conf)
df_conf

df_deaths <- tbl(my_db, sql("select * from covid19_deaths"))
df_deaths <- as.data.frame(df_deaths)
df_deaths

df_recover <- tbl(my_db, sql("select * from covid19_recovered"))
df_recover <- as.data.frame(df_recover)
df_recover

##check the time frame of the data
n.col <- ncol(df_conf)
dates <- names(df_conf)[5:n.col]%>% mdy()
range(dates)
min.date <- min(dates)
max.date <- max(dates)
min.date.txt <- min.date %>% format('%d %b %Y')
max.date.txt <- max.date %>% format('%d %b %Y')

#clean data
cleanData <- function(data) {
  ## remove some columns
  data %<>% select(-c(Province.State, Lat, Long)) %>% rename(country=Country.Region)
  ## convert from wide to long format
  data %<>% gather(key=date, value=count, -country)
  ## convert from character to date
  data %<>% mutate(date = date %>% mdy())
  ## aggregate by country
  data %<>% group_by(country, date) %>% summarise(count=sum(count, na.rm=T)) %>% as.data.frame()
  return(data)
}
## clean the three data sets
data.confirmed <- df_conf %>% cleanData() %>% rename(confirmed=count)
data.deaths <- df_deaths %>% cleanData() %>% rename(deaths=count)
data.recovered <- df_recover %>% cleanData() %>% rename(recovered=count)
data <- data.confirmed %>% merge(data.deaths, all=T) %>% merge(data.recovered, all=T)
data
## countries/regions with confirmed cases, excl. cruise ships
countries <- data %>% pull(country) %>% setdiff('Cruise Ship')
data

data.world <- data %>% group_by(date) %>%
  summarise(country='World',
            confirmed = sum(confirmed, na.rm=T),
            deaths = sum(deaths, na.rm=T),
            recovered = sum(recovered, na.rm=T))
data %<>% rbind(data.world)
data
data %<>% mutate(current.confirmed = confirmed - deaths - recovered)
View(data)

# World confirmed cases map
x <- raw.confirmed
x$confirmed <- x[, ncol(x)]
x %<>% select(c(Country.Region, Province.State, Lat, Long, confirmed)) %>%
mutate(txt=paste0(Country.Region, ' - ', Province.State, ': ', confirmed))
m <- leaflet(width=1200, height=800) %>% addTiles()
# circle marker (units in pixels)
m %<>% addCircleMarkers(x$Long, x$Lat,
                        # radius=2+log2(x$confirmed),
                        radius=0.03*sqrt(x$confirmed),
                        stroke=F,
                        color='red', fillOpacity=0.3,
                        label=x$txt)
# world
m

## China
m %>% setView(95, 35, zoom=4)
## Australia and New Zealand
m %>% setView(135, -27, zoom=4)
## US and Canada
m %>% setView(-105, 40, zoom=4)
## Europe
m %>% setView(10, 50, zoom=4)

# new.confirmed new.deaths new.recovered
data %<>% arrange(country, date)
n <- nrow(data)
day1 <- min(data$date)
data %<>% mutate(new.confirmed = ifelse(date == day1, NA, confirmed - lag(confirmed, n=1)),
                 new.deaths = ifelse(date == day1, NA, deaths - lag(deaths, n=1)),
                 new.recovered = ifelse(date == day1, NA, recovered - lag(recovered, n=1)))
data %<>% mutate(new.confirmed = ifelse(new.confirmed < 0, 0, new.confirmed),
                 new.deaths = ifelse(new.deaths < 0, 0, new.deaths),
                 new.recovered = ifelse(new.recovered < 0, 0, new.recovered))
## death rate based on total deaths and recovered cases
data %<>% mutate(rate.upper = (100 * deaths / (deaths + recovered)) %>% round(1),
                 rate.upper = ifelse(is.nan(rate.upper), 0, rate.upper))

## lower bound: death rate based on total confirmed cases
data %<>% mutate(rate.lower = (100 * deaths / confirmed) %>% round(1),
                 rate.lower = ifelse(is.nan(rate.lower), 0, rate.lower))

## death rate based on the number of death/recovered on every single day
data %<>% mutate(rate.daily = (100 * new.deaths / (new.deaths + new.recovered)) %>% round(1),
                 rate.daily = ifelse(is.nan(rate.daily), 0, rate.daily))

View(data)

## convert from wide to long format
data.long <- data %>%
  select(c(country, date, confirmed, current.confirmed, recovered, deaths)) %>%
  gather(key=type, value=count, -c(country, date))
## set factor levels to show them in a desirable order
data.long %<>% mutate(type=recode_factor(type, confirmed='Total Confirmed',
                                         current.confirmed='Current Confirmed',
                                         recovered='Recovered',
                                         deaths='Deaths'))
View(data.long)

##Number of case World
world <- filter(data.long,country == 'World')
world
plot1 <- world %>% filter(type != 'Total Confirmed') %>%
  ggplot(aes(x=date, y=count)) +
  geom_area(aes(fill=type), alpha=0.5) +
  labs(title=paste0('Numbers of Cases Worldwide - ', max.date.txt)) +
  scale_fill_manual(values=c('red', 'green', 'black')) +
  theme(legend.title=element_blank(), legend.position='bottom',
        plot.title = element_text(size=7),
        axis.title.x=element_blank(),
        axis.title.y=element_blank(),
        legend.key.size=unit(0.2, 'cm'),
        legend.text=element_text(size=6),
        axis.text=element_text(size=7),
        axis.text.x=element_text(angle=45, hjust=1))
plot2 <- world %>%
  ggplot(aes(x=date, y=count)) +
  geom_line(aes(color=type)) +
  labs(title=paste0('Numbers of Cases Worldwide (log scale) - ', max.date.txt)) +
  scale_color_manual(values=c('purple', 'red', 'green', 'black')) +
  theme(legend.title=element_blank(), legend.position='bottom',
        plot.title = element_text(size=7),
        axis.title.x=element_blank(),
        axis.title.y=element_blank(),
        legend.key.size=unit(0.2, 'cm'),
        legend.text=element_text(size=6),
        axis.text=element_text(size=7),
        axis.text.x=element_text(angle=45, hjust=1)) +
  scale_y_continuous(trans='log10')
## show two plots side by side
grid.arrange(plot1, plot2, ncol=2)

gly.plot1 <- ggplotly(plot1)
gly.plot1

gly.plot2 <- ggplotly(plot2)
gly.plot2

## a scatter plot with a smoothed line and vertical x-axis labels
plot1 <- ggplot(data.world, aes(x=date, y=deaths)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Accumulative Deaths') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot2 <- ggplot(data.world, aes(x=date, y=recovered)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Accumulative Recovered Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot3 <- ggplot(data.world, aes(x=date, y=new.deaths)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='New Deaths') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot4 <- ggplot(data.world, aes(x=date, y=new.recovered)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='New Recovered Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
## show four plots together, with 2 plots in each row
grid.arrange(plot1, plot2, plot3, plot4, nrow=2)

## convert from wide to long format, for drawing area plots
rates.long <- data %>%
  select(c(country, date, rate.upper, rate.lower, rate.daily)) %>%
  gather(key=type, value=count, -c(country, date))
# set factor levels to show them in a desirable order
rates.long %<>% mutate(type=recode_factor(type, rate.daily='Daily',
                             rate.upper='Upper bound',
                             rate.lower = 'Lower bound')) 
#View(rates.long) 

g <- rates.long %>% filter(country == "World") %>% 
  ggplot(aes(x = date, y = count, color = type)) + 
  geom_line() +
  labs(title = "World Death Rate (%)") +
  xlab("") + ylab("Death Rate (%)")

g

gly.death <- ggplotly(g)
gly.death

## ranking by confirmed cases
data.latest.all <- data %>% filter(date == max(date)) %>%
  select(country, date,confirmed, new.confirmed, current.confirmed,
         recovered, deaths, new.deaths, death.rate=rate.lower) %>%
  mutate(ranking = dense_rank(desc(confirmed))) %>%
  arrange(ranking)
View(data.latest.all)

k <- 20
## top 20 countries: 21 incl. 'World'
top.countries <- data.latest.all %>% filter(ranking <= k + 1) %>%
  arrange(ranking) %>% pull(country) %>% as.character()
top.countries %>% setdiff('World') %>% print()

data.latest <- data.latest.all %>% filter(!is.na(country)) %>%
  mutate(country=ifelse(ranking <= k + 1, as.character(country), 'Others')) %>%
  mutate(country=country %>% factor(levels=c(top.countries, 'Others')))
data.latest %<>% group_by(country) %>%
  summarise(confirmed=sum(confirmed), new.confirmed=sum(new.confirmed),
            current.confirmed=sum(current.confirmed),
            recovered=sum(recovered), deaths=sum(deaths), new.deaths=sum(new.deaths)) %>%
  mutate(death.rate=(100 * deaths/confirmed) %>% round(1)) 
data.latest
data.latest %<>% select(c(country, confirmed, deaths, death.rate,
                          new.confirmed, new.deaths, current.confirmed,recovered)) %>%
  mutate(recover.rate=(100 * recovered/confirmed) %>% round(1))
data.latest

df_pop <- tbl(my_db, sql("select * from population "))
df_pop <- as.data.frame(df_pop)
df_pop <- rename(df_pop,"country"="Country")

# Add World Population
world_pop <- sum(df_pop$`Population (2020)`)
df_pop[nrow(df_pop) + 1,] = c("World", world_pop)

# Add Other Countries Population
top_pop <- filter(df_pop, df_pop$country %in% top.countries & df_pop$country != "World")
top_pop <- sum(top_pop$`Population (2020)` %>% as.numeric())
others_pop <- (world_pop - top_pop) 
df_pop[nrow(df_pop) + 1,] = c("Others", others_pop)
View(df_pop)

data.latest <- merge(x = data.latest, y = df_pop, by = "country", all.x = TRUE) 
data.latest
data.latest <- rename(data.latest,"population" = "Population (2020)")
data.latest$population <- data.latest$population %>% as.numeric()
data.latest  <- data.latest %>%
  select(c(country, confirmed, deaths, death.rate,
                          new.confirmed, new.deaths,
                          current.confirmed, recovered, recover.rate, population)) %>%
  mutate(confirm.rate = (100 * confirmed / population) %>% round(1))
data.latest

## % of death
data.latest %>% mutate(death.rate=death.rate %>% format(nsmall=1) %>% paste0('%'))

## convert from wide to long format, for drawing area plots
data.latest.long <- data.latest %>% filter(country!='World') %>%
  gather(key=type, value=count, -country)
## set factor levels to show them with proper text and in a desirable order
data.latest.long %<>% mutate(type=recode_factor(type,
                                                confirmed='Total Confirmed',
                                                deaths='Total Deaths',
                                                death.rate='Death Rate (%)',
                                                new.confirmed='New Confirmed (compared with one day before)',
                                                new.deaths='New Deaths (compared with one day before)',
                                                current.confirmed='Current Confirmed',
                                                recover.rate = 'Recovered Rate(%)',
                                                confirm.rate = 'Confirmed Rate(%)'))
#View(data.latest.long)
data.one.dem <- filter(data.latest.long,type=='Total Confirmed'
                       | type=='Total Deaths'
                       | type=='Current Confirmed')
data.two.dem <- filter(data.latest.long,type=='Death Rate (%)'
                     #  | type=='New Confirmed (compared with one day before)'
                    #   | type=='New Deaths (compared with one day before)'
                       | type=='Recovered Rate(%)'
                       | type=='Confirmed Rate(%)')
data.two.dem

## bar chart
data.one.dem %>% ggplot(aes(x=country, y=count, fill=country, group=country)) +
  geom_bar(stat='identity') +
  geom_text(aes(label=count, y=count), size=3, vjust=0) +
  xlab('') + ylab('') +
  labs(title=paste0('Top 20 Countries with Most Confirmed Cases - ', max.date.txt)) +
  scale_fill_discrete(name='Country', labels=aes(count)) +
  theme(legend.title=element_blank(),
        legend.position='none',
        plot.title=element_text(size=13),
        axis.text=element_text(size=8),
        axis.text.x=element_text(angle=45, hjust=1)) +
  facet_wrap(~type, ncol=1, scales='free_y')

data.two.dem$facet <- factor(data.two.dem$type, levels = c('Confirmed Rate(%)', 'Recovered Rate(%)','Death Rate (%)'))
data.two.dem %>% 
  ggplot(aes(x=country, y=count, fill=country, group=country)) +
  geom_bar(stat='identity') +
  geom_text(aes(label=count, y=count), size=4, vjust=0) +
  xlab('') + ylab('') +
  labs(title=paste0('Top 20 Countries with Most Confirmed Cases - ', max.date.txt)) +
  scale_fill_discrete(name='Country', labels=aes(count)) +
  theme(legend.title=element_blank(),
        legend.position='none',
        plot.title=element_text(size=13),
        axis.text=element_text(size=10),
        axis.text.x=element_text(size=10,angle=45, hjust=1)) +
  facet_wrap(~facet, ncol=1, scales='free_y')

##GDP
df_gdp2019 <- tbl(my_db, sql("select * from gdp19"))
df_gdp2019 <- as.data.frame(df_gdp2019)
df_gdp2019

#healthranking
df_healt <- tbl(my_db, sql("select * from healthranking"))
df_healt <- as.data.frame(df_healt)
df_healt <- select(df_healt,c("country","healthCareIndex"))
View(df_healt)

#Top20Pornhub
df_pornhub <- tbl(my_db, sql("select * from Pornhub"))
df_pornhub <- as.data.frame(df_pornhub)
df_pornhub

#temp
df_temp <- tbl(my_db, sql("select * from world_temp"))
df_temp <- as.data.frame(df_temp) 
df_temp$Country[df_temp$Country == "United States"] <- "US"

df_city <- select(df_temp,c("Country","City")) %>%
  rename(country=Country) %>% 
  rename(city=City)

numofcity <- aggregate(city ~ country, data = df_city, length)

df_temp <- select(df_temp,c("Country","Avg_Year")) %>%
  rename(country=Country)
View(df_temp)

#df_temp <- data.frame(country=df_temp[,1],avg=rowMeans(df_temp[,-1]))
df_temp <- df_temp %<>% group_by(country) %>% summarise(avg_temp = mean(Avg_Year,na.rm = TRUE)%>% round(1))
df_temp

##temp bar
#display.brewer.all()
df_temp.all <- df_temp %>% merge(data.latest.all)
View(df_temp.all)
df_temp_top.all <- df_temp.all %>% filter(country %in% top.countries) %>%
  mutate(ranking = ranking - 1) %>%
  arrange(ranking)
View(df_temp_top)
g_temp_top <- df_temp_top %>%
  ggplot(aes(x = reorder(country, ranking), y = avg_temp, fill = avg_temp)) +
  labs(title=paste0("Temperature in Top  20 countries"), subtitle = "Average Temperature in Top 20 countries with most confirmed cases (°C) (2020)") +
  scale_color_gradient(low = "#93DBFF", high = "#FF7771") +
  geom_text(aes(label=avg_temp, y=avg_temp), size=4, vjust=-0.5) +
  geom_bar(stat = "identity", position = "dodge") +
  theme(
        legend.title=element_blank(),
        legend.position='none',
        plot.title=element_text(size = 15, hjust = 0.5),
        plot.subtitle = element_text(size = 12, hjust = 0.5),
        axis.text=element_text(size=8),
        axis.text.x=element_text(size = 9, angle=45, hjust=1)) +
  scale_x_discrete(name = "Country") +
  scale_y_discrete(name = "Average Temperature")

           #labs(title = "Temperature in Top  20 countries", subtitle = "Temperature in Top 20 countries with most confirmed cases (°C)")
         
#g_temp_top         
g_temp_top 

#df_conf
#data.latest.all
lat.long <- rename(df_conf, "country" = "Country.Region", "city" = "Province.State") %>% 
  select("country", "Lat", "Long") %>% 
  merge(df_temp.all[c("country","confirmed", "recovered", "deaths", "avg_temp", "ranking")], by = "country") %>%
  distinct(country, .keep_all = TRUE) %>%
  mutate(ranking = ranking - 1) %>%
  arrange(ranking)
View(lat.long)

label_world <- lat.long 
label_world$avg_temp <- as.numeric(label_world[, names(label_world) %in% c("avg_temp")])
label_world <- label_world %>%  
  mutate(txt=paste0('<b>',ranking, '</b>',
                    '<br/>','<b>',country, '</b>',
                    '<br/>', "Temperature:  ",avg_temp, ' °C',
                    '<br/>', "Confirmed:  ", confirmed, 
                    '<br/>', "Deaths: ", deaths,
                    '<br/>', "Recovered: ", recovered
                    )) 

label_world$txt <- label_world$txt %>% lapply(htmltools::HTML)
label_world 

label_top <- label_world %>% filter(ranking < 21)
label_top

# Temperature Map
wpal <- colorNumeric("YlOrRd", label_world$avg_temp, n = 4)

topIcon <- makeIcon("star.png",
  #iconUrl = "https://static.vecteezy.com/system/resources/previews/001/189/063/non_2x/star-rounded-png.png",
  iconWidth = 10, iconHeight = 10
  #iconAnchorX = 20, iconAnchorY = 20
  
)

label_world <- label_world %>% filter(ranking > 20) 
  
m <- leaflet(width=1200, height=800) %>% addTiles()  
m %<>%  addCircleMarkers(label_world$Long, label_world$Lat,
                        # radius=2+log2(x$confirmed),
                        radius=10,#*log2(m.world$avg.temp),
                        stroke=F,
                        #color='red',
                        color = wpal(label_world$avg_temp), 
                        fillOpacity=0.5,
                        #popup=label.top$txt
                        label= label_world$txt,
                        group = "World"
                        ) %>%
  
  addCircleMarkers(label_top$Long, label_top$Lat,
                        # radius=2+log2(x$confirmed),
                        radius=10,#*log2(m.world$avg.temp),
                        stroke=F,
                        #color='red',
                        color = wpal(label_top$avg_temp), 
                        fillOpacity=0.5,
                        #popup=label.top$txt
                        label= label_top$txt,
                        group = "Top 20 Countries"
                        ) %>%
  
  addLabelOnlyMarkers(label_top$Long, label_top$Lat, label = label_top$ranking,
                      labelOptions = labelOptions(noHide = TRUE, textOnly = TRUE, 
                                                  direction = "head", 
                                                  offset = c(5,4)),
                      group = "Top 20 Countries") %>%
  
  addLegend("bottomright", pal = wpal, values = label_world$avg_temp, opacity = 1,
            labFormat = labelFormat(suffix = " °C"),
            title = "Temperature") %>% 
  
  addLayersControl(
    #baseGroups = c("OSM (default)", "Toner", "Toner Lite"),
    overlayGroups = c("Top 20 Countries", "World"),
    options = layersControlOptions(collapsed = FALSE)
  )
m

gdp.top20 <- df_gdp2019 %>%
  select(c("rank", "country", "GDP (millions of US dollars)")) %>%
  merge(data.latest.all %>% 
          select(country, ranking, confirmed, recovered, deaths) %>% 
          filter(country %in% top.countries & country != "World"), by = "country") %>%
  arrange(ranking) %>% 
  mutate(ranking = ranking - 1) 
gdp.top20 %<>% rename("GDP" = "GDP (millions of US dollars)")
gdp.top20

g <- ggplot(gdp.top20, aes(x = GDP, y = reorder(country, -ranking))) +
  geom_histogram(stat = "identity", aes(fill = GDP))+ 
  scale_fill_gradient("GDP", low = "#FF4038", high = "#50E952") + 
  labs(title=paste0("GDP  of Top 20 Countries in 2019 (millions of US dollars)")) +
  geom_text(aes(label=GDP, x = GDP), size=3.5, hjust=-0.2) +
  xlab("GDP (millions of US dollars)") +
  ylab("") +
  theme(legend.title=element_blank())
g

gly.top.gdp <- ggplotly(g)
gly.top.gdp

# Pornhub
df_pornhub <- tbl(my_db, sql("select * from pornhub"))
df_pornhub <- as.data.frame(df_pornhub)
df_pornhub

# Sars
df_sars <- tbl(my_db, sql("select * from sars_2003"))
df_sars <- as.data.frame(df_sars)
View(df_sars)

dates.s <- df_sars[,1]%>% mdy()
range(dates.s)
min.date.s <- min(dates.s)
max.date.s <- max(dates.s)
min.date.txt.s <- min.date.s %>% format('%d %b %Y')
max.date.txt.s <- max.date.s %>% format('%d %b %Y')

#rate
data.sars %<>% arrange(country, date)
n <- nrow(data.sars)
day1.sars <- min(data.sars$date)
data.sars %<>% mutate(new.confirmed = ifelse(date == day1.sars, NA, confirmed - lag(confirmed, n=1)),
                 new.deaths = ifelse(date == day1.sars, NA, deaths - lag(deaths, n=1)),
                 new.recovered = ifelse(date == day1.sars, NA, recovered - lag(recovered, n=1)))
data.sars %<>% mutate(new.confirmed = ifelse(new.confirmed < 0, 0, new.confirmed),
                 new.deaths = ifelse(new.deaths < 0, 0, new.deaths),
                 new.recovered = ifelse(new.recovered < 0, 0, new.recovered))
## death rate based on total deaths and recovered cases
data.sars %<>% mutate(rate.upper = (100 * deaths / (deaths + recovered)) %>% round(1),
                 rate.upper = ifelse(is.nan(rate.upper), 0, rate.upper))

## lower bound: death rate based on total confirmed cases
data.sars %<>% mutate(rate.lower = (100 * deaths / confirmed) %>% round(1),
                 rate.lower = ifelse(is.nan(rate.lower), 0, rate.lower))

## death rate based on the number of death/recovered on every single day
data.sars %<>% mutate(rate.daily = (100 * new.deaths / (new.deaths + new.recovered)) %>% round(1),
                 rate.daily = ifelse(is.nan(rate.daily), 0, rate.daily))

View(data.sars)

## convert from wide to long format
data.sars.long <- data.sars %>%
  select(c(country, date, confirmed, current.confirmed, recovered, deaths)) %>%
  gather(key=type, value=count, -c(country, date))
## set factor levels to show them in a desirable order
data.sars.long %<>% mutate(type=recode_factor(type, confirmed='Total Confirmed',
                                         current.confirmed='Current Confirmed',
                                         recovered='Recovered',
                                         deaths='Deaths'))
View(data.sars.long)

# World sars' long data 
world.sars.long <- data.sars.long %>%
  filter(country == "World")
View(world.sars.long)

g <- ggplot(world.sars.long, aes(date, count, color = type)) +
  geom_line()+
  labs(title = "Number of Cases Worldwide: SARs")+
  xlab("")+
  ylab("")
g

gly.g <- ggplotly(g)
gly.g
gly.plot2

df_sars_lastdate <- data.sars %>%
  filter(date == max.date.s)

df_sars_lastdate

## Current Confirmed Cases
data.sars.world <- data.sars %>% filter(country=='World')
#View(data.sars.world)
n <- nrow(data.sars.world)
View(data.sars.world)
plot1 <- ggplot(data.sars.world, aes(x=date, y=current.confirmed)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Current Confirmed Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot2 <- ggplot(data.world, aes(x=date, y=new.confirmed)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Daily New Confirmed Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
## show two plots side by side
grid.arrange(plot1, plot2, ncol=2)

## a scatter plot with a smoothed line and vertical x-axis labels
plot1 <- ggplot(data.sars.world, aes(x=date, y=deaths)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Accumulative Deaths') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot2 <- ggplot(data.sars.world, aes(x=date, y=recovered)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Accumulative Recovered Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot3 <- ggplot(data.sars.world, aes(x=date, y=new.deaths)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='New Deaths') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot4 <- ggplot(data.sars.world, aes(x=date, y=new.recovered)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='New Recovered Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
## show four plots together, with 2 plots in each row
grid.arrange(plot1, plot2, plot3, plot4, nrow=2)

## a scatter plot with a smoothed line and vertical x-axis labels
plot1 <- ggplot(data.sars.world, aes(x=date, y=deaths)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Accumulative Deaths') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot2 <- ggplot(data.sars.world, aes(x=date, y=recovered)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='Accumulative Recovered Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot3 <- ggplot(data.sars.world, aes(x=date, y=new.deaths)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='New Deaths') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
plot4 <- ggplot(data.sars.world, aes(x=date, y=new.recovered)) +
  geom_point() + geom_smooth() +
  xlab('') + ylab('Count') + labs(title='New Recovered Cases') +
  theme(axis.text.x=element_text(angle=45, hjust=1))
## show four plots together, with 2 plots in each row
grid.arrange(plot1, plot2, plot3, plot4, nrow=2)

#Top 20 with gdp
data.longGDP <- df_gdp %>% gather(key=year, value=GDP, -c(country))
data.top <- data.latest %>% filter(!country %in% c('World', 'Others'))
data.top <- head(data.top,20)
View(data.latest)
data.gdp <- filter(data.longGDP,year=='2020')
df_sars_lastdate_confirmed <- df_sars_lastdate %>%
  select("country", "confirmed") %>%
  rename(sars = "confirmed")
View(df_sars_lastdate_confirmed)
#merge
mergcountry = function(data1,data2){
  data <- merge(x = data1, y = data2, by = "country", all.x = TRUE) 
  return(data)
}
data.top.world <- merge(x = data.top, y = df_gdp2019, by = "country", all.x = TRUE) %>% 
  select(-c(code,rank,new.confirmed,new.deaths,current.confirmed,population)) %>% 
  rename(GDP="GDP (millions of US dollars)")

data.top.world <- merge(x = data.top.world, y = df_healt, by = "country", all.x = TRUE) %>%
  rename(healthcare="healthCareIndex")
#data.top.world <- mergcountry(data.top.world, df_temp)

data.top.world <- merge(x = data.top.world, y = df_pornhub, by = "country", all.x = TRUE) %>%
  rename(Pornhub = "PornhubIndex(%)")

data.top.world <- merge(x = data.top.world, y = df_sars_lastdate_confirmed, by = "country", all.x = TRUE) 


data.top.world <- mergcountry(data.top.world, df_temp)
index <- is.na(data.top.world)
data.top.world[index] <- 0
data.top.world
View(data.top.world)


normalize = function(data){
  #return ((data - min(data,na.rm = TRUE))/(max(data,na.rm = TRUE) - min(data,na.rm = TRUE)))
  z <- scale(data);
  tanh(z/2)
}
norm_data = as.data.frame(apply(data.top.world[,2:13],2,normalize))
corr_data <- norm_data
norm_data$country <- c("Argentina","Bangladesh","Brazil","Chile","Colombia","France","Germany","India","Iran","Italy","Mexico","Pakistan","Peru","Russia","saudi Arabia","South Africa","Spain","Turkey","United Kingdom","US")
#View(norm_data)


norm_data_plot <- select(norm_data,"country","confirm.rate","death.rate","recover.rate","healthcare","Pornhub","GDP","avg_temp", "sars")
norm_data_plot %<>% gather(key=type, value=count, -c(country))
level_order <- factor(norm_data_plot$type, 
                      level = c("sars","Pornhub","GDP","avg_temp","healthcare","recover.rate","death.rate","confirm.rate"))
ggplot(data = norm_data_plot, aes(x=country, y=level_order, fill=count)) + 
  geom_tile() +
  scale_fill_gradient(low = "pink", high = "blue") +
  xlab("") +
  ylab("") +
  theme_bw() +
  theme(axis.text.x = element_text(angle = 90,vjust = 1))+
  theme(
    axis.line = element_blank(),
    axis.ticks = element_blank(),
    panel.grid.minor = element_blank(),
    panel.grid.major = element_blank(),
    panel.border = element_blank(),
    panel.background = element_blank(),
    #legend.position = "none"
  )

#correlation
corr_data %<>% select(c(GDP,confirm.rate,death.rate,recover.rate,healthcare,avg_temp,Pornhub, sars))
head(corr_data)
cor(corr_data)
ggcorrplot(cor(corr_data),hc.order = TRUE,
           outline.color = "white",
           colors = c("#6D9EC1","white","#E46726"),
           lab = TRUE)

## Data in US

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 

df_us <- tbl(my_db, sql("select * from covidus"))
df_us <- as.data.frame(df_us) 
df_us <- select(df_us, date, state, cases, deaths)

df_us_pop <- tbl(my_db, sql("select * from data_population")) 
df_us_pop <- as.data.frame(df_us_pop)

df_us_gender <- tbl(my_db, sql("select * from data_gender")) 
df_us_gender <- as.data.frame(df_us_gender)

df_us_ethnic <- tbl(my_db, sql("select * from data_ethnic")) 
df_us_ethnic <- as.data.frame(df_us_ethnic)

df_us_lockdown <- tbl(my_db, sql("select * from data_lockdown")) 
df_us_lockdown <- as.data.frame(df_us_lockdown)

df_us_health <- tbl(my_db, sql("select * from data_health")) 
df_us_health <- as.data.frame(df_us_health)

df_us_testing <- tbl(my_db, sql("select * from data_testing")) 
df_us_testing <- as.data.frame(df_us_testing)

df_us_latlong <- tbl(my_db, sql("select * from us_latlong"))
df_us_latlong <- as.data.frame(df_us_latlong)

#View(df_us_latlong)

data.us <- df_us  %>%
  mutate(date = date %>% mdy()) %>%
  rename("confirmed" = "cases")
#data.us

dates.us <- data.us[,1]
range(dates.us)

[1] "2020-01-21" "2020-12-24"

min.date.us <- min(dates.us)
max.date.us <- max(dates.us)
min.date.txt.us <- min.date.us %>% format('%d %b %Y')
max.date.txt.us <- max.date.us %>% format('%d %b %Y')
day1.us <- min(data.us$date)

data.us.total <- data.us %>% group_by(date) %>%
  summarise(state='US',
            confirmed = sum(confirmed, na.rm=T),
            deaths = sum(deaths, na.rm=T))

`summarise()` ungrouping output (override with `.groups` argument)

#View(data.us.total)
data.us %<>% rbind(data.us.total)
View(data.us)

data.us.long <- data.us %>% 
  gather(key = type, value = count, -c(date, state)) 
#data.us.long

us.total <- data.us.total %>%
  mutate(new.confirmed = ifelse(date == day1, 0, confirmed - lag(confirmed, n=1)),
                new.deaths = ifelse(date == day1, 0, deaths - lag(deaths, n=1)))

us.total %<>% mutate(new.confirmed = ifelse(new.confirmed < 0, 0, new.confirmed),
                 new.deaths = ifelse(new.deaths < 0, 0, new.deaths))
us.total

us.total.long <- us.total %>% 
  gather(key = type, value = count, -c(date, state))

View(us.total.long)

g1 <- data.us.long %>% filter(state == "US") %>%

Warning message:
Unknown or uninitialised column: `Country`. 

  ggplot(aes(x = date, y = count)) +
  geom_area(aes(fill=type), alpha=0.5) +
  labs(title = paste0("Cumulative cases in US : ", min.date.txt.us, '-', max.date.txt.us, " (Log Scale)"))  +
  scale_y_continuous(trans='log10')+
  scale_fill_manual(values=c('red', 'black'))+
  ylab("")

g2 <- us.total.long %>%
  filter(type %in% c("new.confirmed")) %>%
  ggplot(aes(x = date, y = count, color = type)) + 
  geom_line() + 
  labs(title = paste0("Daily confirmed cases in US : ", min.date.txt.us, '-', max.date.txt.us)) +
  xlab("Date") +
  ylab("Confirmed cases")

gly.us1 <- ggplotly(g1)

Transformation introduced infinite values in continuous y-axis

gly.us2 <- ggplotly(g2)

gly.us1

gly.us2

NA

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 

data.us %<>% mutate(new.confirmed = ifelse(date == day1, NA, confirmed - lag(confirmed, n=1)),
                 new.deaths = ifelse(date == day1, NA, deaths - lag(deaths, n=1)))

data.us %<>% mutate(new.confirmed = ifelse(new.confirmed < 0, 0, new.confirmed),
                 new.deaths = ifelse(new.deaths < 0, 0, new.deaths))
View(data.us)

#data.us.daily <- data.us %>% filter(state == "US")
#View(data.us.daily)

data.us.pop <- df_us_pop %>% select(State, Population) %>%
  rename("state" = "State") 
#data.us.pop

data.us.gender <- df_us_gender %>% select(State, Male, Female) %>%
  rename("state" = "State") 
data.us.gender

data.us.latest <- data.us %>%
  filter(date == max.date.us) %>% 
  merge(data.us.pop, by = "state", all.x = T) %>%
  merge(data.us.gender, by = "state", all.x = T)
#View(data.us.latest)

data.us.latest$Population[data.us.latest$state == "US"] <- sum(data.us.pop$Population)
data.us.latest %<>% mutate(ranking = dense_rank(desc(confirmed)),
                           confirmed.rate = (100 * confirmed / Population) %>% round(2),
                           death.rate = (100 * deaths / confirmed) %>% round(2),
                           Male.confirmed = (Male * confirmed) %>% round(0),
                           Female.confirmed = Female * confirmed %>% round(0),
                           Male.deaths = Male * deaths,
                           Female.deaths = Female * deaths) %>%
  arrange(ranking) 

top.us <- data.us.latest[,1]
top.us

 [1] "US"                       "California"               "Texas"                    "Florida"                  "Illinois"                
 [6] "New York"                 "Ohio"                     "Georgia"                  "Pennsylvania"             "Tennessee"               
[11] "Michigan"                 "Wisconsin"                "North Carolina"           "Indiana"                  "Arizona"                 
[16] "New Jersey"               "Minnesota"                "Missouri"                 "Massachusetts"            "Alabama"                 
[21] "Virginia"                 "Colorado"                 "Louisiana"                "South Carolina"           "Iowa"                    
[26] "Oklahoma"                 "Maryland"                 "Utah"                     "Kentucky"                 "Washington"              
[31] "Kansas"                   "Nevada"                   "Arkansas"                 "Mississippi"              "Connecticut"             
[36] "Nebraska"                 "Idaho"                    "New Mexico"               "Oregon"                   "Puerto Rico"             
[41] "South Dakota"             "North Dakota"             "Rhode Island"             "Montana"                  "West Virginia"           
[46] "Delaware"                 "Alaska"                   "Wyoming"                  "New Hampshire"            "District of Columbia"    
[51] "Maine"                    "Hawaii"                   "Guam"                     "Vermont"                  "Virgin Islands"          
[56] "Northern Mariana Islands"

View(top.us)


data.us.latest

NA

# List of top 20 state

Warning message:
Unknown or uninitialised column: `Country`. 

k <- 20
data.us.top <- data.us.latest %>%
  filter(ranking <= k+1) %>% 
  arrange(ranking) 
View(data.us.top)

us.state.top <- data.us.top %>% pull(state) %>% as.character()
us.state.top  %>% setdiff('US') %>% print()

 [1] "California"     "Texas"          "Florida"        "Illinois"       "New York"       "Ohio"           "Georgia"        "Pennsylvania"  
 [9] "Tennessee"      "Michigan"       "Wisconsin"      "North Carolina" "Indiana"        "Arizona"        "New Jersey"     "Minnesota"     
[17] "Missouri"       "Massachusetts"  "Alabama"        "Virginia"      

# confirmed rate & death rate of top 20 state
g.rate <- data.us.latest %>% filter(state %in% us.state.top & state != "US") %>%
  select(state, confirmed.rate, death.rate, ranking) %>%
  gather(key = Type, value = Percent, -c(state, ranking)) %>%
  ggplot(aes(x=reorder(state, -desc(ranking)), y=Percent, fill = Percent)) +
  geom_bar(stat='identity') +
  scale_fill_gradient(low = "#ebbc62", high = "#b42006") +
  geom_text(aes(label=Percent, y=Percent), size=3, vjust=0) +
  xlab('') + ylab('') +
  labs(title=paste0('Confirmed Rate & Death Rate of Top 20 State in US')) +
  #scale_fill_continuous(name='State', labels=aes(Percent)) +
  theme(legend.title=element_blank(),
        legend.position='none',
        plot.title=element_text(size=13),
        axis.text=element_text(size=8),
        axis.text.x=element_text(angle=45, hjust=1)) +
  facet_wrap(~Type, ncol=1, scales='free_y') 

g.rate

us.confirmed.num <- data.us.top$confirmed[data.us.top$state == "US"] %>% as.numeric()
us.confirmed.num

data.us.top %<>% mutate(confirmed.per.us = (confirmed * 100 / us.confirmed.num) %>% round(1))
data.us.top

g.us.top1 <- data.us.top %>%
  filter(state != "US") %>%
  select(state, confirmed, confirmed.per.us, ranking) %>%
  gather(key = Type, value = count, -c(state, ranking, confirmed.per.us)) %>%
  
  ggplot(aes(fill = count, y = count, x = reorder(state, -desc(ranking)))) + 
  geom_bar(position = "dodge", stat = "identity") +
  labs(title = "20 state in US with most confirmed cases") +
  scale_fill_gradient(low = "#ebbc62", high = "#b42006") +
  xlab("") + 
  ylab("Confirmed Cases") +
  geom_text(aes(label=paste0(confirmed.per.us, "%")), size=3, vjust=-0.5) +
  theme(axis.text.x = element_text(angle = 90,vjust = 0.5))+
  theme(
    axis.line = element_blank(),
    axis.ticks = element_blank(),
    panel.grid.minor = element_blank(),
    panel.grid.major = element_blank(),
    panel.border = element_blank(),
    panel.background = element_blank(),
    #legend.position = "none"
  )

g.us.top1

gly.us.top1 <- ggplotly(g.us.top1)
gly.us.top1

df_us_all <- df_us

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 
3: Unknown or uninitialised column: `Country`. 

#gender in us
df_us_gender <- tbl(my_db, sql("select * from data_gender"))
df_us_gender <- as.data.frame(df_us_gender)
df_us_gender <- select(df_us_gender,c("State","Male","Female"))
df_us_gender <- rename(df_us_gender,"state"="State")
df_us_gender

#population in us
df_us_pop <- tbl(my_db, sql("select * from data_population"))
df_us_pop <- as.data.frame(df_us_pop)
df_us_pop <- select(df_us_pop,c("State","Population"))
df_us_pop <- rename(df_us_pop,"state"="State")
df_us_pop

#lockdown in us
df_us_lockdown <- tbl(my_db, sql("select * from data_lockdown"))
df_us_lockdown <- as.data.frame(df_us_lockdown)
df_us_lockdown <- select(df_us_lockdown,c("State","Day lockdown"))
df_us_lockdown <- rename(df_us_lockdown,"state"="State")
df_us_lockdown

#GDP in us
df_us_gdp <- tbl(my_db, sql("select * from us_gdp"))
df_us_gdp  <- as.data.frame(df_us_gdp )
df_us_gdp  <- select(df_us_gdp ,c("State","GDPs"))
df_us_gdp  <- rename(df_us_gdp ,"state"="State")
df_us_gdp 

#homeless in us
df_us_homeless <- tbl(my_db, sql("select * from us_homeless"))
df_us_homeless <- as.data.frame(df_us_homeless)
df_us_homeless <- select(df_us_homeless,c("State","Homeless"))
df_us_homeless <- rename(df_us_homeless,"state"="State")
df_us_homeless

#View(df_us)

#merge
mergcountry = function(data1,data2){
  data <- merge(x = data1, y = data2, by = "state", all.x = TRUE) 
  return(data)
}

data.top.state <- data.us.top %>% filter(state != "US") %>%
  select(state, confirmed, deaths)
#View(data.top.state)

df_Allus <- mergcountry(df_us, df_us_gender)

df_Allus <- mergcountry(df_Allus, df_us_pop) 

df_Allus <- mergcountry(df_Allus, df_us_lockdown) 

df_Allus <- mergcountry(df_Allus, df_us_gdp)

df_Allus <- mergcountry(df_Allus, df_us_homeless)

View(df_Allus)

index <- is.na(df_Allus)

Warning message:
Unknown or uninitialised column: `Country`. 

df_Allus[index] <- 0

normalize = function(data){
  #return ((data - min(data,na.rm = TRUE))/(max(data,na.rm = TRUE) - min(data,na.rm = TRUE)))
  z <- scale(data);
  tanh(z/2)
}
Allus = as.data.frame(apply(df_Allus[,2:9],2,normalize))

Error in colMeans(x, na.rm = TRUE) : 'x' must be numeric

Allus$state <- c(df_Allus$state)

Warning message:
Unknown or uninitialised column: `Country`. 

View(Allus)

corr_dataUS <- rename(corr_dataUS ,"Daylockdown" = "Day lockdown")

#correlation

Warning messages:
1: Unknown or uninitialised column: `Country`. 
2: Unknown or uninitialised column: `Country`. 

corr_dataUS %<>% select(c(cases,deaths,Male,Female,Population,Daylockdown,GDPs,Homeless))
head(corr_dataUS)

cor(corr_dataUS)

                cases    deaths      Male    Female Population Daylockdown      GDPs  Homeless
cases       1.0000000 0.8771162 0.1273080 0.1904992  0.6171785   0.2038334 0.6075674 0.5556301
deaths      0.8771162 1.0000000 0.1147696 0.1847547  0.6601563   0.2104893 0.6777292 0.6083036
Male        0.1273080 0.1147696 1.0000000 0.9513769  0.1956974   0.5064869 0.1828444 0.1390017
Female      0.1904992 0.1847547 0.9513769 1.0000000  0.2679789   0.5403218 0.2419723 0.1905770
Population  0.6171785 0.6601563 0.1956974 0.2679789  1.0000000   0.3394907 0.9855365 0.8879380
Daylockdown 0.2038334 0.2104893 0.5064869 0.5403218  0.3394907   1.0000000 0.3062257 0.2385315
GDPs        0.6075674 0.6777292 0.1828444 0.2419723  0.9855365   0.3062257 1.0000000 0.9114065
Homeless    0.5556301 0.6083036 0.1390017 0.1905770  0.8879380   0.2385315 0.9114065 1.0000000

ggcorrplot(cor(corr_dataUS),hc.order = TRUE,
           outline.color = "white",
           colors = c("#6D9EC1","white","#E46726"),
           lab = TRUE)